﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.Collections;

namespace VietKinsNutrition.Common
{
    public class DataAccess
    {
        #region ATTRIBUTES
        public static string dbType = "SQL";

        private static SqlTransaction sql_trans;
        private static OleDbTransaction acc_trans;

        SqlCommand _sqlCommand;
        OleDbCommand _oledbCommand;
        public static bool isSQL
        {
            get { return dbType == "SQL"; }
        }
        public static bool isAcc
        {
            get { return dbType == "Acc"; }
        }
        #endregion

        #region CONSTRUCTORS
        static DataAccess()
        {
            sql_trans = null;
            acc_trans = null;
        }
        #endregion

        #region PRIVATE METHODS
        /// <summary>
        /// Gán giá trị cho các parameter của câu lệnh (lấy danh sách các parameter nếu là SP)
        /// </summary>
        private static void AssignedParameters(SqlCommand command, object[] parameterValues, bool isSP)
        {
            if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
            if (isSP)
            {
                SqlCommandBuilder.DeriveParameters(command);
            }
            else
            {
                Regex rx = new Regex(@"@[^@]\S\w*");
                ArrayList al = new ArrayList();

                foreach (Match m in rx.Matches(command.CommandText))
                {
                    if (al.IndexOf(m.Value) < 0)
                    {
                        al.Add(m.Value);
                        SqlParameter param = new SqlParameter();
                        param.ParameterName = m.Value.Remove(0, 1);
                        param.Direction = ParameterDirection.InputOutput;
                        command.Parameters.Add(param);
                    }
                }
            }

            if (command.Parameters.Count > 0)
            {
                if ((parameterValues == null) || (parameterValues.Length == 0)) throw new ArgumentNullException("Must have value to pass for parameter values!");

                int i = 0;
                foreach (SqlParameter parameter in command.Parameters)
                {
                    if ((parameter.Direction != ParameterDirection.Output) && (parameter.Direction != ParameterDirection.ReturnValue))
                    {
                        parameter.Value = (parameterValues[i] == null) ? DBNull.Value : parameterValues[i];
                        i++;
                    }
                }
            }
        }
        private static void AssignedParameters(OleDbCommand command, object[] parameterValues, bool isSP)
        {
            if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
            if (isSP)
            {
                OleDbCommandBuilder.DeriveParameters(command);
            }
            else
            {
                Regex rx = new Regex(@"@[^@]\S\w*");
                ArrayList al = new ArrayList();

                foreach (Match m in rx.Matches(command.CommandText))
                {
                    if (al.IndexOf(m.Value) < 0)
                    {
                        al.Add(m.Value);
                        OleDbParameter param = new OleDbParameter();
                        param.ParameterName = m.Value.Remove(0, 1);
                        param.Direction = ParameterDirection.InputOutput;
                        command.Parameters.Add(param);
                    }
                }
            }

            if (command.Parameters.Count > 0)
            {
                if ((parameterValues == null) || (parameterValues.Length == 0)) throw new ArgumentNullException("Must have value to pass for parameter values!");

                int i = 0;
                foreach (OleDbParameter parameter in command.Parameters)
                {
                    if ((parameter.Direction != ParameterDirection.Output) && (parameter.Direction != ParameterDirection.ReturnValue))
                    {
                        parameter.Value = (parameterValues[i] == null) ? DBNull.Value : parameterValues[i];
                        i++;
                    }
                }
            }
        }
        private static SqlCommand CreateCommand(SqlConnection connection, string commandText, object[] parameterValues, bool isSP, bool beginTrans)
        {
            SqlCommand command = new SqlCommand(commandText, connection);
            if (isSP) command.CommandType = CommandType.StoredProcedure;
            if (beginTrans)
            {
                sql_trans = connection.BeginTransaction();
                command.Transaction = sql_trans;
            }

            if ((parameterValues != null) && (parameterValues.Length > 0)) DataAccess.AssignedParameters(command, parameterValues, isSP);
            return command;
        }
        private static OleDbCommand CreateCommand(OleDbConnection connection, string commandText, object[] parameterValues, bool isSP, bool beginTrans)
        {
            OleDbCommand command = new OleDbCommand(commandText, connection);
            if (isSP) command.CommandType = CommandType.StoredProcedure;
            if (beginTrans)
            {
                acc_trans = connection.BeginTransaction();
                command.Transaction = acc_trans;
            }

            if ((parameterValues != null) && (parameterValues.Length > 0)) DataAccess.AssignedParameters(command, parameterValues, isSP);
            return command;
        }


        /// <summary>
        /// Thực hiện câu lệnh truyền vào với các giá trị parameter cho trước
        /// </summary>
        private static int Execute(string connectionString, string commandText, object[] parameterValues, bool isSP)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, false);
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                int iResult = command.ExecuteNonQuery();
                try
                {
                    parameterValues.SetValue(command.Parameters[command.Parameters.Count - 1].Value, parameterValues.Length - 1);
                }
                catch { }
                command.Connection.Close();
                return iResult;
            }
        }


        private static int Execute(string connectionString, string commandText, object[] parameterValues, bool isSP, bool bFlag)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, false);
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                object obj;
                int iResult;
                if (bFlag)
                {
                    obj = command.ExecuteScalar();
                    iResult = int.Parse(obj.ToString());
                }
                else
                {
                    iResult = command.ExecuteNonQuery();
                }

                command.Connection.Close();
                return iResult;
            }
        }

        /// <summary>
        /// Thực hiện câu lệnh truyền vào với các giá trị parameter cho trước và lấy về các parameter
        /// </summary>
        public static SqlParameterCollection GetParameters(string connectionString, string commandText, object[] parameterValues, bool isSP)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, false);
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
                return command.Parameters;
            }
        }

        /// <summary>
        /// Thực hiện câu lệnh và fill kết quả vào datatable
        /// </summary>
        private static DataTable FillDataTable(SqlCommand command)
        {
            using (SqlDataAdapter dAdapter = new SqlDataAdapter(command))
            {
                DataTable dTable = new DataTable();

                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                dAdapter.Fill(dTable);
                command.Connection.Close();
                return dTable;
            }
        }
        private static DataTable FillDataTable(OleDbCommand command)
        {
            using (OleDbDataAdapter dAdapter = new OleDbDataAdapter(command))
            {
                DataTable dTable = new DataTable();

                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                dAdapter.Fill(dTable);
                command.Connection.Close();
                return dTable;
            }
        }
        /// <summary>
        /// Thực hiện câu lệnh và fill một mảng của kết quả vào datatable
        /// </summary>
        private static DataTable FillDataTableSpecial(SqlCommand command, int startRow, int maxRow, string tableName)
        {
            using (SqlDataAdapter dAdapter = new SqlDataAdapter(command))
            {
                DataSet dSet = new DataSet();

                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                dAdapter.Fill(dSet, startRow, maxRow, tableName);
                command.Connection.Close();
                return dSet.Tables[tableName];
            }
        }

        /// <summary>
        /// Thực hiện câu lệnh và fill kết quả vào dataset
        /// </summary>
        private static DataSet FillDataSet(SqlCommand command)
        {
            using (SqlDataAdapter dAdapter = new SqlDataAdapter(command))
            {
                DataSet dSet = new DataSet();

                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                dAdapter.Fill(dSet);
                command.Connection.Close();
                return dSet;
            }
        }
        #endregion

        #region PUBLIC METHODS
        /// <summary>
        /// Thực hiện câu lệnh query và trả về số bản ghi đã cập nhật
        /// </summary>
        public static int ExecuteNonQuery(string connectionString, string commandText)
        {
            return DataAccess.Execute(connectionString, commandText, null, false);
        }

        public static int ExecuteNonQuery(string connectionString, string commandText, object parameterValue)
        {
            return DataAccess.Execute(connectionString, commandText, new object[] { parameterValue }, false);
        }

        public static int ExecuteNonQuery(string connectionString, string commandText, object[] parameterValues)
        {
            return DataAccess.Execute(connectionString, commandText, parameterValues, false);
        }

        /// <summary>
        /// Thực hiện gọi SP và trả về số bản ghi đã cập nhật
        /// </summary>
        public static int ExecuteStoreProcedure(string connectionString, string commandText)
        {
            return DataAccess.Execute(connectionString, commandText, null, true);
        }

        public static int ExecuteStoreProcedure(string connectionString, string commandText, object parameterValue)
        {
            return DataAccess.Execute(connectionString, commandText, new object[] { parameterValue }, true);
        }

        public static int ExecuteStoreProcedure(string connectionString, string commandText, object[] parameterValues)
        {
            return DataAccess.Execute(connectionString, commandText, parameterValues, true);
        }

        //Tra ve ID 
        public static int ExecuteStoreProcedure(string connectionString, string commandText, object[] parameterValues, bool flag)
        {
            return DataAccess.Execute(connectionString, commandText, parameterValues, true, flag);
        }

        /// <summary>
        /// Thực hiện câu lệnh và trả về giá trị của 1 parameter cần thiết (nếu tên parameter là rỗng thì trả về giá trị @ReturnValue trong SP hoặc null trong câu query)
        /// </summary>
        public static object ExecuteGetParameter(string connectionString, string commandText, string parameterToGet, bool isSP)
        {
            return DataAccess.ExecuteGetParameter(connectionString, commandText, null, isSP);
        }

        public static object ExecuteGetParameter(string connectionString, string commandText, string parameterToGet, object parameterValue, bool isSP)
        {
            return DataAccess.ExecuteGetParameter(connectionString, commandText, parameterToGet, new object[] { parameterValue }, isSP);
        }

        public static object ExecuteGetParameter(string connectionString, string commandText, string parameterToGet, object[] parameterValues, bool isSP)
        {
            SqlParameterCollection parameters = DataAccess.GetParameters(connectionString, commandText, parameterValues, isSP);
            if (parameterToGet == "") return isSP ? parameters[0].Value : null;
            return (parameters.IndexOf(parameterToGet) > -1) ? parameters[parameterToGet].Value : null;
        }

        /// <summary>
        /// Thực hiện câu lệnh và trả về các giá trị của parameter cần thiết (nếu tên parameter không có thì trả về null)
        /// </summary>
        public static Hashtable ExecuteGetParameters(string connectionString, string commandText, string[] parametersToGet, bool isSP)
        {
            return DataAccess.ExecuteGetParameters(connectionString, commandText, parametersToGet, null, isSP);
        }

        public static Hashtable ExecuteGetParameters(string connectionString, string commandText, string[] parametersToGet, object parameterValue, bool isSP)
        {
            return DataAccess.ExecuteGetParameters(connectionString, commandText, parametersToGet, new object[] { parameterValue }, isSP);
        }

        public static Hashtable ExecuteGetParameters(string connectionString, string commandText, string[] parametersToGet, object[] parameterValues, bool isSP)
        {
            SqlParameterCollection parameters = DataAccess.GetParameters(connectionString, commandText, parameterValues, isSP);

            Hashtable h = new Hashtable();
            foreach (string param in parametersToGet)
            {
                if (parameters.IndexOf(param) > -1) h.Add(param, parameters[param]);
                else h.Add(param, null);
            }
            return h;
        }

        /// <summary>
        /// Thực hiện câu lệnh và trả về các giá trị của parameter output
        /// </summary>
        public static Hashtable ExecuteGetAllOutput(string connectionString, string commandText)
        {
            return DataAccess.ExecuteGetAllOutput(connectionString, commandText, null);
        }

        public static Hashtable ExecuteGetAllOutput(string connectionString, string commandText, object parameterValue)
        {
            return DataAccess.ExecuteGetAllOutput(connectionString, commandText, new object[] { parameterValue });
        }

        public static Hashtable ExecuteGetAllOutput(string connectionString, string commandText, object[] parameterValues)
        {
            SqlParameterCollection parameters = DataAccess.GetParameters(connectionString, commandText, parameterValues, true);

            Hashtable h = new Hashtable();
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter.Direction != ParameterDirection.Input) h.Add(parameter.ParameterName, parameter.Value);
            }
            return h;
        }

        /// <summary>
        /// Thực hiện câu lệnh sql và fill kết quả vào datatable
        /// </summary>
        public static DataTable FillTable(string connectType, string connectionString, string commandText)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                return DataAccess.FillDataTable(DataAccess.CreateCommand(connection, commandText, null, false, false));
            }
        }

        public static DataTable FillTable(string connectionString, string commandText, bool isSP)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return DataAccess.FillDataTable(DataAccess.CreateCommand(connection, commandText, null, isSP, false));
            }
        }

        public static DataTable FillTable(string connectionString, string commandText, object parameterValue, bool isSP)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand command = DataAccess.CreateCommand(connection, commandText, new object[] { parameterValue }, isSP, false);
                return DataAccess.FillDataTable(command);
            }
        }

        public static DataTable FillTable(string connectionString, string commandText, object[] parameterValues, bool isSP)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, false);
                return DataAccess.FillDataTable(command);
            }
        }

        /// <summary>
        /// Thực hiện câu lệnh sql và fill 1 mảng kết quả theo index cho trước vào 1 datatable có tên định trước
        /// </summary>
        public static DataTable FillTableSpecial(string connectionString, string commandText, int startRow, int maxRow, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, null, false, false);
                return DataAccess.FillDataTableSpecial(command, startRow, maxRow, tableName);
            }
        }

        public static DataTable FillTableSpecial(string connectionString, string commandText, bool isSP, int startRow, int maxRow, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, null, isSP, false);
                return DataAccess.FillDataTableSpecial(command, startRow, maxRow, tableName);
            }
        }

        public static DataTable FillTableSpecial(string connectionString, string commandText, object parameterValue, bool isSP, int startRow, int maxRow, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, new object[] { parameterValue }, isSP, false);
                return DataAccess.FillDataTableSpecial(command, startRow, maxRow, tableName);
            }
        }

        public static DataTable FillTableSpecial(string connectionString, string commandText, object[] parameterValues, bool isSP, int startRow, int maxRow, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, false);
                return DataAccess.FillDataTableSpecial(command, startRow, maxRow, tableName);
            }
        }

        /// <summary>
        /// Thực hiện câu lệnh sql và fill kết quả vào dataset
        /// </summary>
        public static DataSet FillDataSet(string connectionString, string commandText)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, null, false, false);
                return DataAccess.FillDataSet(command);
            }
        }

        public static DataSet FillDataSet(string connectionString, string commandText, bool isSP)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, null, isSP, false);
                return DataAccess.FillDataSet(command);
            }
        }

        public static DataSet FillDataSet(string connectionString, string commandText, object parameterValue, bool isSP)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, new object[] { parameterValue }, isSP, false);
                return DataAccess.FillDataSet(command);
            }
        }

        public static DataSet FillDataSet(string connectionString, string commandText, object[] parameterValues, bool isSP)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, false);
                return DataAccess.FillDataSet(command);
            }
        }

        /// <summary>
        /// Fill cấu trúc của 1 table theo tên table đó trong database
        /// </summary>
        public static DataTable FillTableSchema(string connectionString, DataTable dTable)
        {
            string commandString = "SELECT TOP 1 * FROM " + dTable.TableName;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandString, connection);
                using (SqlDataAdapter dAdapter = new SqlDataAdapter(command))
                {
                    connection.Open();
                    dAdapter.FillSchema(dTable, SchemaType.Source);
                    connection.Close();
                    return dTable;
                }
            }
        }

        public static DataTable FillTableSchema(string connectionString, string tableName)
        {
            DataTable dTable = new DataTable(tableName);
            string commandString = "SELECT TOP 1 * FROM " + tableName;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandString, connection);
                using (SqlDataAdapter dAdapter = new SqlDataAdapter(command))
                {
                    connection.Open();
                    dAdapter.FillSchema(dTable, SchemaType.Source);
                    connection.Close();
                    return dTable;
                }
            }
        }
        #endregion

        #region TRANSACTION METHODS
        /// <summary>
        /// Hoàn thành transaction và kết thúc hoạt động của nó
        /// </summary>
        public static void CommitTransaction()
        {
            if (sql_trans != null)
            {
                sql_trans.Commit();
                sql_trans.Dispose();
                sql_trans = null;
            }
            if (acc_trans != null)
            {
                acc_trans.Commit();
                acc_trans.Dispose();
                acc_trans = null;
            }
        }


        /// <summary>
        /// Quay lại thời điểm ban đầu hoặc savepoint (theo tên đã định nghĩa) của transaction và kết thúc hoạt động của nó nếu quay lại từ đầu
        /// </summary>
        public static void RollbackTransaction()
        {
            if (sql_trans != null)
            {
                sql_trans.Rollback();
                sql_trans.Dispose();
                sql_trans = null;
            }
            if (acc_trans != null)
            {
                acc_trans.Rollback();
                acc_trans.Dispose();
                acc_trans = null;
            }
        }

        public static void RollbackTransaction(string pointName)
        {
            if (sql_trans != null) sql_trans.Rollback(pointName);
            if (acc_trans != null) acc_trans.Rollback();
        }

        /// <summary>
        /// Lưu lại savepoint cho thời điểm hiện tại của transaction
        /// </summary>
        public static void SavePoint(string pointName)
        {
            if (sql_trans != null) sql_trans.Save(pointName);
        }

        /// <summary>
        /// Bắt đầu 1 transaction và thực hiện câu lệnh sql
        /// </summary>
        public static int ExecuteBeginTrans(string connectionString, string commandText)
        {
            return DataAccess.ExecuteBeginTrans(connectionString, commandText, null, false);
        }

        public static int ExecuteBeginTrans(string connectionString, string commandText, bool isSP)
        {
            return DataAccess.ExecuteBeginTrans(connectionString, commandText, null, isSP);
        }

        public static int ExecuteBeginTrans(string connectionString, string commandText, object parameterValue, bool isSP)
        {
            return DataAccess.ExecuteBeginTrans(connectionString, commandText, new object[] { parameterValue }, isSP);
        }

        public static int ExecuteBeginTrans(string connectionString, string commandText, object[] parameterValues, bool isSP)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                if (sql_trans != null)
                {
                    sql_trans.Dispose();
                    sql_trans = null;
                }

                SqlCommand command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, true);
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                int iResult = command.ExecuteNonQuery();
                command.Connection.Close();
                return iResult;
            }
        }

        public static int ExecuteCommitTrans(string connectionString, string commandText, bool rollbackOnError)
        {
            return DataAccess.ExecuteCommitTrans(connectionString, commandText, null, false, rollbackOnError);
        }

        public static int ExecuteCommitTrans(string connectionString, string commandText, bool isSP, bool rollbackOnError)
        {
            return DataAccess.ExecuteCommitTrans(connectionString, commandText, null, isSP, rollbackOnError);
        }

        public static int ExecuteCommitTrans(string connectionString, string commandText, object parameterValue, bool isSP, bool rollbackOnError)
        {
            return DataAccess.ExecuteCommitTrans(connectionString, commandText, new object[] { parameterValue }, isSP, rollbackOnError);
        }

        public static int ExecuteCommitTrans(string connectionString, string commandText, object[] parameterValues, bool isSP, bool rollbackOnError)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command;
                if (sql_trans != null) command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, false);
                else command = DataAccess.CreateCommand(connection, commandText, parameterValues, isSP, rollbackOnError);

                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                int iResult = command.ExecuteNonQuery();

                if ((iResult < 1) && rollbackOnError) DataAccess.RollbackTransaction();
                else DataAccess.CommitTransaction();

                command.Connection.Close();
                return iResult;
            }
        }
        #endregion
    }
}
